/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2011
 */

package com.tech15.core.dao;

import static cn.org.rapid_framework.util.ObjectUtils.isNotEmpty;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javacommon.base.BaseHibernateDao;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import cn.org.rapid_framework.page.Page;
import cn.org.rapid_framework.page.PageRequest;

import com.tech15.core.model.Dept;
import com.tech15.core.vo.query.DeptQuery;

@Repository
public class DeptDao extends BaseHibernateDao<Dept,Long>{

	public Class getEntityClass() {
		return Dept.class;
	}
	
	/**
	 * 按层级查询部门信息
	 * @return
	 */
	public List<Dept> findLevelDept(){
		String sql = "select level,t.deptid,t.deptname,t.pid from sys_dept t  start with t.pid=0 connect by prior t.deptid=t.pid order by order by level asc";
		Map map = new HashMap();
		map.put("deptid", "Long");
		map.put("deptname", "String");
		map.put("pid", "Long");
		return findSqlAll(sql, map, Dept.class);
	}
	
	public List<Dept> findAllParentByDid(){
		String hql = "from Dept t where t.pid is null order by t.sortno asc";
		return getHibernateTemplate().find(hql);
	}
	/**
	 * 装载树状结构 父子对象描述
	 * @return
	 */
	public Dept loadTree(Long deptid){
		return getHibernateTemplate().load(Dept.class, deptid);
	}
	/**
	 * 装载树状结构 从根目录查询列表形式排列
	 * @return
	 */
	public List<Dept> selectDept(){
		String sql = "select t.*,t.deptname name,t.deptid id,ltrim(sys_connect_by_path(t.deptname,'-'),'-') as orgInfo from sys_dept t  where level<3 " +
					 " start with t.pid is null connect by pid=prior deptid";
		Map map = new HashMap();
		map.put("orgInfo","String");
		map.put("deptid","Long");
		map.put("id","Long");
		map.put("deptname","String");
		map.put("name","String");
		map.put("pid","Long");
		map.put("sortno","Integer");
		return findSqlAll(sql, map, Dept.class);
	}
	/**
	 * 装载树状结构 从根目录查询列表形式排列，并按照部门id和部门名称查询
	 * @return
	 */
	public List<Dept> selectDeptByDeptid(Long deptid,String deptname){
		String sql = "select t.*,t.deptname name,t.deptid id,ltrim(sys_connect_by_path(t.deptname,'-'),'-') as orgInfo from sys_dept t  where 1=1 ";
		if(isNotEmpty(deptid) && deptid.longValue() != 0) {
            sql += " and (t.pid ="+deptid+" or t.deptid="+deptid+") ";
        }else{
        	sql += " and t.pid is null ";
        }
		if(isNotEmpty(deptname)) {
            sql += " where t.deptname like '%"+deptname+"%' ";
        }
		sql += " start with t.pid is null connect by pid=prior deptid  ";
		
		Map map = new HashMap();
		map.put("orgInfo","String");
		map.put("deptid","Long");
		map.put("id","Long");
		map.put("deptname","String");
		map.put("name","String");
		map.put("pid","Long");
		map.put("sortno","Integer");
		return findSqlAll(sql, map, Dept.class);
	}
	/**
	 * 装载树状结构 最近使用的部门列表
	 * @return
	 */
	public List<Dept> quickSelectDept(){
		String sql = "select (select ltrim(max(sys_connect_by_path(deptname,'-')),'-') from sys_dept where deptid=t.deptid start with deptid=1 connect by pid=prior deptid) orgInfo,t.deptname name,t.deptid id,t.* from sys_dept t where t.pid=1";
		Map map = new HashMap();
		map.put("orgInfo","String");
		map.put("deptid","Long");
		map.put("id","Long");
		map.put("deptname","String");
		map.put("name","String");
		map.put("pid","Long");
		map.put("sortno","Integer");
		return findSqlAll(sql, map, Dept.class);
	}
	/**
	 * 装载树状结构 level形式展现，目前没用
	 * @return
	 */
	public List<Dept> findDeptTree(){
		return (List<Dept>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(final Session session) throws HibernateException, SQLException {
				String sql = "select level,t.* from sys_dept t start with t.pid=0 connect by prior t.deptid = t.pid";
				SQLQuery query =  (SQLQuery)session.createSQLQuery(sql).setResultTransformer(new AliasToBeanResultTransformer(Dept.class));
				query.addScalar("level",Hibernate.INTEGER);
				return query.list();
			}
		});
	}
	
	public Page findPage(DeptQuery query) {
		String sql = "select t from Dept t where 1=1 "
			  	+ "/~ and t.sortno = {sortno} ~/"
			  	+ "/~ and t.deptname = {deptname} ~/"
			  	+ "/~ and t.email = {email} ~/"
			  	+ "/~ and t.tel = {tel} ~/"
			  	+ "/~ and t.taxno = {taxno} ~/"
			  	+ "/~ and t.memo = {memo} ~/"
			  	+ "/~ and t.pid = {pid} ~/"
				+ "/~ order by [sortColumns] ~/";

		return pageQuery(sql,query);
	}
	
}
